package com.etc;

import java.sql.*;

public class TestJDBC {
    public static void testInsertDept(int deptno, String dname, String loc) {
        String sql="insert into dept values(?,?,?)";
        int result= DbUtil.executeUpdate(sql,deptno,dname,loc);
        System.out.println(result>0?"success":"fail");
    }

    //更新记录，给定deptNp,更新dname,loc
    public static void testUpdatedept(int deptno, String dname, String loc) {
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
                    "scott", "tiger");
            statement = connection.createStatement();
            String sql = "update dept set dname='" + dname + "',loc='" + loc + "' where deptno=" + deptno;
            int result = statement.executeUpdate(sql);
            System.out.println(result > 0 ? "success" : "fail");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }

    public static void testDeletedept(int deptno) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
                    "scott", "tiger");
            String sql = "delete from dept where deptno = ?";
            pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, deptno);
            int result = pstmt.executeUpdate();
            System.out.println(result > 0 ? "success" : "fail");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void testSelectDept() {
        Connection connection = null;
        PreparedStatement pstmt = null;
        try {
            //加载驱动类
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //获得connection
            connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
                    "scott", "tiger");

            String sql = "select * from dept";
            pstmt = connection.prepareStatement(sql);
            //返回数据库中受影响的行数
            ResultSet resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getInt(1) + "\t" +
                        resultSet.getString(2) + "\t" +
                        resultSet.getString("loc"));
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void testSelectDeptByPk(int deptno) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        try {
            //加载驱动类
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //获得connection
            connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
                    "scott", "tiger");

            String sql = "select * from dept where deptno = ?";
            pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, deptno);
            //返回数据库中受影响的行数
            ResultSet resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getInt(1) + "\t" +
                        resultSet.getString(2) + "\t" +
                        resultSet.getString("loc"));
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //emp表插入一条记录 数据值自定义
    //insert into emp values(8002,'SGX','CLERK',7902,to_date('1999/04/17','yyyy/mm/dd'),800.00,200.00,20);
    public static void testInsertEmp(int empno, String ename, String job, int mgr, String date, int sal, int comm, int deptno) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        try {
            //加载驱动类
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //获得connection
            connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
                    "scott", "tiger");

            String sql = "insert into emp values(?,?,?,?,to_date(?,'yyyy/mm/dd'),?,?,?)";
            pstmt = connection.prepareStatement(sql);

            pstmt.setInt(1, empno);
            pstmt.setString(2, ename);
            pstmt.setString(3, job);
            pstmt.setInt(4, mgr);
            pstmt.setString(5, date);
            pstmt.setInt(6, sal);
            pstmt.setInt(7, comm);
            pstmt.setInt(8, deptno);
            //返回数据库中受影响的行数
            int result = pstmt.executeUpdate();
            System.out.println(result > 0 ? "success" : "fail");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void testUpdateEmp(int empno) {
        Connection connection = null;
        PreparedStatement pstat = null;
        PreparedStatement pstat1 = null;
        ResultSet resultSet = null;
        try {
            //加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //获取连接
            connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
                    "scott", "tiger");
            String sql = "select * from emp where empno=?";
            String sql1 = "update emp set sal=? where empno=?";
            //获取pstar
            pstat = connection.prepareStatement(sql);
            pstat.setInt(1, empno);
            resultSet = pstat.executeQuery();
            if (resultSet.next()) {
                int sal = resultSet.getInt("sal");
                pstat1 = connection.prepareStatement(sql1);
                pstat1.setInt(1, sal + 200);
                pstat1.setInt(2, empno);
                int result = pstat1.executeUpdate();
                System.out.println(result > 0 ? "success" : "fail");

            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (pstat != null) {
                try {
                    pstat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (pstat1 != null) {
                try {
                    pstat1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void testDeleteEmp(int empno) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
                    "scott", "tiger");
            String sql = "delete from emp where empno = ?";
            pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, empno);
            int result = pstmt.executeUpdate();
            System.out.println(result > 0 ? "success" : "fail");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    public static void main(String[] args) {
          testInsertDept(70,"BJQ","AH");
//        testUpdatedept(50,"IT2","JL2");
//        testDeletedept(50);
       testSelectDept();
//        testSelectDeptByPk(10);
//        testInsertEmp(8001,"ZHANGSAN","CLERK",7902,"2000/01/01",800,200,20);
        //testUpdateEmp(8001);
//        testDeleteEmp(8001);
    }
}
